package cn.topevery.garden.wapi.utils;

import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.util.ArrayList;
import java.util.List;


/**
 * 描述 公园导入Excel工具类
 *
 * @author wenzhao.lin
 * @created 2021 -07-27 17:11:29
 */
public class ParkExcelDataUtil {


    /**
     * 描述 获取Excel表格数据
     *
     * @param file      the file
     * @param sheetName the sheet name
     * @throws Exception the exception
     * @author wenzhao.lin
     * @created 2021 -07-27 18:04:07
     */
    public static List<List<String>> getExcelData(MultipartFile file, String sheetName)throws Exception {
        String fileName = file.getOriginalFilename();
        List<List<String>> list = new ArrayList<>();
        if(fileName.endsWith(".xls")){
            list = getExcelDataByXls(file, sheetName);
        }
        if(fileName.endsWith(".xlsx")){
            list = getExcelDataByXlsx(file, sheetName);
        }
        return list;
    }

    /**
     * 描述 获取Excel数据(.xls格式).
     *
     * @param file      the file
     * @param sheetName the sheet name
     * @throws Exception the exception
     * @author wenzhao.lin
     * @created 2021 -07-27 17:11:29 Read excel data.
     */
    private static List<List<String>> getExcelDataByXls(MultipartFile file, String sheetName) throws Exception {
        HSSFWorkbook sheets = new HSSFWorkbook(file.getInputStream());
        //获取sheet
        HSSFSheet sheet = sheets.getSheet(sheetName);
        //获取行数
        int rows = sheet.getPhysicalNumberOfRows();
        List<List<String>> dataListAll = new ArrayList<>();
        List<String> dataList = new ArrayList<>();
        for(int i=1;i<rows;i++){
            //获取列数
            HSSFRow row = sheet.getRow(i);
            int columns = row.getPhysicalNumberOfCells();
            for(int j=0;j<columns;j++){
                if(row.getCell(j) != null){
                    row.getCell(j).setCellType(CellType.STRING); //读取前将单元格设置为文本类型读取
                }

                if(row.getCell(j)==null||row.getCell(j).equals("")){
                    dataList.add(null);
                }else {
                    String cell = row.getCell(j).toString();
                    dataList.add(cell);
                }
            }
            dataListAll.add(dataList);
            dataList = new ArrayList<>();
        }
        return dataListAll;
    }


    /**
     * 描述 获取Excel数据(.xlsx格式).
     *
     * @param file      the file
     * @param sheetName the sheet name
     * @throws Exception the exception
     * @author wenzhao.lin
     * @created 2021 -07-27 17:48:23
     */
    private static List<List<String>> getExcelDataByXlsx(MultipartFile file, String sheetName) throws Exception {
        XSSFWorkbook sheets = new XSSFWorkbook(file.getInputStream());
        //获取sheet
        XSSFSheet sheet = sheets.getSheet(sheetName);
        //获取行数
        int rows = sheet.getPhysicalNumberOfRows();
        List<List<String>> dataListAll = new ArrayList<>();
        List<String> dataList = new ArrayList<>();
        for(int i=1;i<rows;i++){
            //获取列数
            XSSFRow row = sheet.getRow(i);
            int columns = row.getPhysicalNumberOfCells();
            for(int j=0;j<columns;j++){
                if(row.getCell(j) != null){
                    row.getCell(j).setCellType(CellType.STRING); //读取前将单元格设置为文本类型读取
                }
                String cell = row.getCell(j).toString();
                dataList.add(cell);
            }
            dataListAll.add(dataList);
            dataList = new ArrayList<>();
        }
        return dataListAll;
    }
}
